package com.cn.hotel.dao.Impl;

import com.cn.hotel.dao.IRoomDao;
import com.cn.hotel.model.RoomModel;
import com.cn.hotel.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RoomImpl implements IRoomDao {
    /**
     * 添加房间 含图片
     *
     * @param roomModel
     * @return
     */
    @Override
    public int add(RoomModel roomModel) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into rooms(room_id,room_type,room_price,room_title,room_introduce,room_photo) " +
                " values('" + roomModel.getRoomId() + "','" + roomModel.getRoomType() + "','" + roomModel.getRoomPrice() + "'," +
                "'" + roomModel.getRoomTitle() + "','" + roomModel.getRoomIntroduce() + "','" + roomModel.getRoomPhoto() + "')");
        return JDBC.executeUpdate(sql.toString());
    }

    /**
     * 查询所有房间  和根据条件查询
     *
     * @param roomModel
     * @return
     */
    @Override
    public List<RoomModel> findAll(RoomModel roomModel) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from rooms where 1=1 ");
        if (!"".equals(roomModel.getRoomType()) && "全部".equals(roomModel.getRoomType())) {
            sql.append(" and room_type = '" + roomModel.getRoomType() + "'");
        }
        if (!"".equals(roomModel.getStartRoomPrice())&&"".equals(roomModel.getEndRoomPrice())){
            sql.append(" and room_price > " + roomModel.getStartRoomPrice());
        }
        if ("".equals(roomModel.getStartRoomPrice())&&!"".equals(roomModel.getEndRoomPrice())){
            sql.append(" and room_price < " + roomModel.getEndRoomPrice());
        }
        if (!"".equals(roomModel.getStartRoomPrice())&&!"".equals(roomModel.getEndRoomPrice())){
            sql.append(" and room_price between '"+roomModel.getStartRoomPrice()+"' and '"+roomModel.getEndRoomPrice()+"'");
        }
        if (!"".equals(roomModel.getRoomTitle()) && roomModel.getRoomTitle() != null) {
            sql.append(" and room_title like '%" + roomModel.getRoomTitle() + "%'");
        }
        sql .append( " order by  room_id " + " limit  "+(roomModel.getPageModel().getPageNum()-1)*roomModel.getPageModel().getPageSize()+"," +
                ""+roomModel.getPageModel().getPageSize()+"");
        List<RoomModel> list = new ArrayList<>();
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        try {
            while (resultSet.next()) {
                RoomModel model = new RoomModel();
                model.setRoomId(resultSet.getInt("room_id"));
                model.setRoomType(resultSet.getString("room_type"));
                model.setRoomPrice(resultSet.getDouble("room_price"));
                model.setRoomTitle(resultSet.getString("room_title"));
                model.setRoomPhoto(resultSet.getString("room_photo"));
                model.setRoomIntroduce(resultSet.getString("room_introduce"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 得到数据库总条数
     *
     * @param roomModel
     * @return
     */
    @Override
    public int getCount(RoomModel roomModel) {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) count from rooms where 1=1 ");
        if (!"".equals(roomModel.getRoomType()) && "全部".equals(roomModel.getRoomType())) {
            sql.append(" and room_type = '" + roomModel.getRoomType() + "'");
        }
        if (!"".equals(roomModel.getStartRoomPrice())&&"".equals(roomModel.getEndRoomPrice())){
            sql.append(" and room_price > " + roomModel.getStartRoomPrice());
        }
        if ("".equals(roomModel.getStartRoomPrice())&&!"".equals(roomModel.getEndRoomPrice())){
            sql.append(" and room_price < " + roomModel.getEndRoomPrice());
        }
        if (!"".equals(roomModel.getStartRoomPrice())&&!"".equals(roomModel.getEndRoomPrice())){
            sql.append(" and room_price between '"+roomModel.getStartRoomPrice()+"' and '"+roomModel.getEndRoomPrice()+"'");
        }
        if (!"".equals(roomModel.getRoomTitle()) && roomModel.getRoomTitle() != null) {
            sql.append(" and room_title like '%" + roomModel.getRoomTitle() + "%'");
        }
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        int count = 0;
        try {
            resultSet.next();
            count = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;

    }

    /**
     * 根据roomId查询数据 返回给页面  进行赋值
     *
     * @param
     * @return
     */
    @Override
    public RoomModel findByRoomId(Integer roomId) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from rooms where room_id = " + roomId);
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        RoomModel model = new RoomModel();
        try {
            while (resultSet.next()) {

                model.setRoomId(resultSet.getInt("room_id"));
                model.setRoomType(resultSet.getString("room_type"));
                model.setRoomPrice(resultSet.getDouble("room_price"));
                model.setRoomTitle(resultSet.getString("room_title"));
                model.setRoomPhoto(resultSet.getString("room_photo"));
                model.setRoomIntroduce(resultSet.getString("room_introduce"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    /**
     * 根据roomId进行数据删除
     *
     * @param temp
     * @return
     */
    @Override
    public int delete(Integer temp) {
        StringBuilder sql = new StringBuilder();
        sql.append("delete  from rooms where room_id=" + temp);
        int count = JDBC.executeUpdate(sql.toString());
        return count;
    }
    /**
     * 提交的编辑后的房间信息  进行存储更改
     * @param roomModel
     * @return
     */
    @Override
    public int edit(RoomModel roomModel) {
        StringBuilder sql = new StringBuilder();
        sql.append("update rooms set");
        if (roomModel.getRoomType()!=""){
            sql.append(" room_type= '"+roomModel.getRoomType()+"',");
        }
        if (roomModel.getRoomTitle()!=""){
            sql.append(" room_title='"+roomModel.getRoomTitle()+"',");
        }
        sql.append(" room_price='"+roomModel.getRoomPrice()+"',");
        sql.append(" room_photo ='"+roomModel.getRoomPhoto()+"',");
        sql.append(" room_introduce ='"+roomModel.getRoomIntroduce()+"',");
        sql.append(" remark='' where room_id ="+roomModel.getRoomId());
        return JDBC.executeUpdate(sql.toString());
    }
//---------------------------------------------------------------------前端调值
    @Override
    public List<RoomModel> getRoomModel() {
        StringBuilder sql = new StringBuilder("select * from rooms");
        List<RoomModel> list = new ArrayList<>();
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        try {
            while (resultSet.next()) {
                RoomModel model = new RoomModel();
                model.setRoomPrice(resultSet.getDouble("room_price"));
                model.setRoomTitle(resultSet.getString("room_title"));
                model.setRoomPhoto(resultSet.getString("room_photo"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<RoomModel> getRoomModelList() {
        StringBuilder sql = new StringBuilder("select * from rooms");
        List<RoomModel> list = new ArrayList<>();
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        try {
            while (resultSet.next()) {
                RoomModel model = new RoomModel();
                model.setRoomTitle(resultSet.getString("room_title"));
                model.setRoomPhoto(resultSet.getString("room_photo"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 获取指定数量的房间数目
     * @return
     */
    @Override
    public List<RoomModel> getAppointRoomModel() {
        StringBuilder sql = new StringBuilder("select * from rooms order by room_id desc limit 6");
        List<RoomModel> list = new ArrayList<>();
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        try {
            while (resultSet.next()) {
                RoomModel model = new RoomModel();
                model.setRoomTitle(resultSet.getString("room_title"));
                model.setRoomPhoto(resultSet.getString("room_photo"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 网站首页获取三张房间图
     * @return
     */
    @Override
    public List<RoomModel> getThree() {
        StringBuilder sql = new StringBuilder("SELECT * FROM home_room_picture ORDER BY id DESC LIMIT 3");
        List<RoomModel> list = new ArrayList<>();
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        try {
            while (resultSet.next()) {
                RoomModel model = new RoomModel();
                model.setRoomPhoto(resultSet.getString("photo"));
                model.setHomeBackGroud(resultSet.getString("backgroud_img"));
                model.setHomeBackTitle(resultSet.getString("title"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
